package cn.edu.glut.user.repository;


import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import cn.edu.glut.entity.Teacher;
import cn.edu.glut.vo.TeacherToAdminVO;
/**
 * 
 * @author Caizhfy
 * @version v.0.1
 * @createTime 2017年12月15日下午6:50:45
 * @email Caizhfy@163.com
 * @description
 *
 */
public interface TeacherRepository extends JpaRepository<Teacher, String>,JpaSpecificationExecutor<Teacher> {
	
	Teacher findByUsername(String username);
	
	//用于admin管理显示的list
	@Query("select new cn.edu.glut.vo.TeacherToAdminVO(t.id, t.username, t.nickname, sum(case when se.isSelection=1 then 1 else 0 end) as chooseNumber) from Teacher as t "
			+ " left join t.subjects as su left join su.selections as se group by t.id")
	Page<TeacherToAdminVO> findAllTeacherVO(Pageable pageable);
	//用于显示给学生的,根据学生的专业分一下,节省篇幅就不用动态查询了
	@Query("select new cn.edu.glut.vo.TeacherToAdminVO(t.id, t.username, t.nickname, sum(case when se.isSelection=1 then 1 else 0 end) as chooseNumber) from Teacher as t "
			+ " left join t.subjects as su left join su.selections as se where su.major.id=(select s.major.id from Student s where s.id=:studentId) group by t.id")
	Page<TeacherToAdminVO> findAllTeacherVO(Pageable pageable, @Param("studentId")String studentId);

	@Query("select new cn.edu.glut.vo.TeacherToAdminVO(t.id, t.username, t.nickname, sum(case when se.isSelection=1 then 1 else 0 end) as chooseNumber) from Teacher as t "
			+ " left join t.subjects as su left join su.selections as se  where t.username=:username group by t.id")
	TeacherToAdminVO findTeacherToAdminVOByUsername(@Param("username")String username);

	
}
